package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hngm.bean.Newssheet;
import com.hngm.bean.Product;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

/**
 * 新闻数据访问层
 */
public class NewssheetDao {
	/**
	 * 根据产品类型查询对应的产品列表
	 */
	public List<Newssheet> queryNewssheetByType(String nType) {
		List<Newssheet> ntList = new ArrayList<Newssheet>();
		// 定义sql语句
		String sql = "select * from newssheet where newstype_id = " + nType;
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String newssheadlines = rs.getString("newssheadlines");
				String newscontent = rs.getString("newscontent");
				String newsprofile = rs.getString("newsprofile");
				String newstime = rs.getString("newstime");
				long newstype_id = rs.getLong("newstype_id");
				int recommend = rs.getInt("recommend");
				int sort = rs.getInt("sort");
				int del = rs.getInt("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Newssheet n = new Newssheet(id, newssheadlines, newscontent, newsprofile, newstime, newstype_id,
						recommend, sort, creationtime, updatetime);
				ntList.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return ntList;
	}

	/**
	 * 根据新闻ID查询新闻内容的方法
	 */
	public Newssheet queryNewssheetInfoById(String pid) {
		Newssheet n = new Newssheet();
		// 定义sql语句
		String sql = "select * from newssheet where id = " + pid;
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();
		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String newssheadlines = rs.getString("newssheadlines");
				String newscontent = rs.getString("newscontent");
				String newsprofile = rs.getString("newsprofile");
				String newstime = rs.getString("newstime");
				long newstype_id = rs.getLong("newstype_id");
				int recommend = rs.getInt("recommend");
				int sort = rs.getInt("sort");
				int del = rs.getInt("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				n = new Newssheet(id, newssheadlines, newscontent, newsprofile, newstime, newstype_id, recommend, sort,
						creationtime, updatetime);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return n;
	}

	public List<Newssheet> queryNewssheetList() {
		List<Newssheet> nesList = new ArrayList<Newssheet>();
		// 定义查询的SQL语句
		String sql = "select * from newssheet";
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();
		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String newssheadlines = rs.getString("newssheadlines");
				String newscontent = rs.getString("newscontent");
				String newsprofile = rs.getString("newsprofile");
				String newstime = rs.getString("newstime");
				long newstype_id = rs.getLong("newstype_id");
				int recommend = rs.getInt("recommend");
				int sort = rs.getInt("sort");
				int del = rs.getInt("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Newssheet nes = new Newssheet(id, newssheadlines, newscontent, newsprofile, newstime, newstype_id,
						recommend, sort, creationtime, updatetime);
				nesList.add(nes);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return nesList;
	}

	public ArrayList<Newssheet> querynewsListByParam(String newsname, String del, String orderby, int beginIndex,
			int limit) {
		ArrayList<Newssheet> nwsList = new ArrayList<Newssheet>();
		// 定义sql语句
		// 将传过来的查询条件作为where条件：拼接到sql语句中
		StringBuffer sb = new StringBuffer("select * from newssheet where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(newsname)) {
			sb.append(String.format(" and newssheadlines like '%s'", "%" + newsname + "%"));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		if (StringUtil.isNotNullorEmpty(orderby)) {
			if ("newstimeasc".equals(orderby)) {
				sb.append(" order by newstime asc ");
			} else if ("newstimedesc".equals(orderby)) {
				sb.append(" order by newstime desc ");
			}
		}
		sb.append(String.format(" limit %d,%d ", beginIndex, limit));
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String newssheadlines = rs.getString("newssheadlines");
				String newscontent = rs.getString("newscontent");
				String newsprofile = rs.getString("newsprofile");
				String newstime = rs.getString("newstime");
				long newstype_id = rs.getLong("newstype_id");
				int recommend = rs.getInt("recommend");
				int sort = rs.getInt("sort");
				String del1 = rs.getString("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Newssheet nes = new Newssheet(id, newssheadlines, newscontent, newsprofile, newstime, newstype_id,
						recommend, sort, creationtime, updatetime);
				nwsList.add(nes);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return nwsList;
	}

	public int querynewsListByParamCount(String newsname, String del, String orderby) {
		// 定义sql语句
		StringBuffer sb = new StringBuffer("select count(1) as count  from newssheet where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(newsname)) {
			sb.append(String.format(" and newssheadlines like '%s'", "%" + newsname + "%"));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}

	public static int insertNewsconten(Newssheet p) {
		String sql = String.format("insert into newssheet(newssheadlines,newscontent,newsprofile,newstype_id) values ('%s','%s','%s',%d)", p.getNewssheadlines(),p.getNewscontent(),p.getNewsprofile(),p.getNewstype_id());
		return DBManager.executeUpdate(sql);
	}

	public int delnewssheet(String newsId) {
		//定义删除产品的sql语句
		String sql = "delete from newssheet where id = " + newsId;
		return DBManager.executeUpdate(sql);
	}

	public static int editNewsconten(Newssheet p) {
		String sql = String.format("update newssheet set "
				+ "newssheadlines='%s',"
				+ "newscontent='%s',"
				+ "newsprofile='%s',"
				+ "newstype_id=%d "
				+ "where id = %d",
				p.getNewssheadlines(),
				p.getNewscontent(),
				p.getNewsprofile(),
				p.getNewstype_id(),
				p.getId());
		return DBManager.executeUpdate(sql);
	}
}
